Skip Headers

Oracle® XML DB Developer's Guide 10g Release 2 (10.2) Part Number B14259-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 22 SQL Access Using RESOURCE_VIEW and PATH_VIEW

This chapter describes the predefined public views, `RESOURCE_VIEW` and `PATH_VIEW`, that provide access to Oracle XML DB repository data. It discusses SQL functions `under_path` and `equals_path` that query resources based on their path names and `path` and `depth` that return resource path names and depths, respectively.

This chapter contains these topics:

- Overview of Oracle XML DB RESOURCE_VIEW and PATH_VIEW

- RESOURCE_VIEW and PATH_VIEW APIs

- Using the RESOURCE_VIEW and PATH_VIEW APIs

- Working with Multiple Oracle XML DB Resources

- Performance Tuning of Oracle XML DB Resource Queries

- Searching for Resources Using Oracle Text

## Overview of Oracle XML DB RESOURCE_VIEW and PATH_VIEW

Figure 22-1 shows how Oracle XML DB `RESOURCE_VIEW` and `PATH_VIEW` provide a mechanism for using SQL to access data stored in Oracle XML DB Repository. Data stored in the repository using protocols such as FTP and WebDAV, or using application program interfaces (APIs), can be accessed in SQL using `RESOURCE_VIEW` values and `PATH_VIEW` values.

`RESOURCE_VIEW` consists of a resource, itself an `XMLType`, that contains the name of the resource that can be queried, its ACLs, and its properties, static or extensible.

- If the content comprising the resource is XML, stored somewhere in an `XMLType` table or view, then the `RESOURCE_VIEW` points to the `XMLType` row that stores the content.

- If the content is not XML, then the `RESOURCE_VIEW` stores it as a LOB.

Parent-child relationships between folders (necessary to construct the hierarchy) are maintained and traversed efficiently using the hierarchical index. Text indexes are available to search the properties of a resource, and internal B*Tree indexes over Names and ACLs speed up access to these attributes of the Resource `XMLType`.

`RESOURCE_VIEW` and `PATH_VIEW`, along with PL/SQL package `DBMS_XDB`, provide all query-based access to Oracle XML DB and DML functionality that is available through the API.

The base table for `RESOURCE_VIEW` is `XDB.XDB$RESOURCE`. This table should only be accessed through `RESOURCE_VIEW` or the `DBMS_XDB` API.

See Also: Chapter 3, "Using Oracle XML DB"

Figure 22-1 Accessing Repository Resources Using RESOURCE_VIEW and PATH_VIEW

Description of "Figure 22-1 Accessing Repository Resources Using RESOURCE_VIEW and PATH_VIEW"

### RESOURCE_VIEW Definition and Structure

The `RESOURCE_VIEW` contains one row for each resource in Oracle XML DB Repository. Table 22-1 describes its structure.

Table 22-1 Structure of RESOURCE_VIEW

Column | Datatype | Description
--- | --- | ---
`RES` | `XMLType` | A resource in the repository
`ANY_PATH` | `VARCHAR2` | An (absolute) path to the resource
`RESID` | `RAW` | Resource OID, which is a unique handle to the resource

### PATH_VIEW Definition and Structure

The `PATH_VIEW` contains one row for each unique path to access a resource in Oracle XML DB Repository. Table 22-2 describes its structure.

Table 22-2 Structure of PATH_VIEW

Column | Datatype | Description
--- | --- | ---
`PATH` | `VARCHAR2` | An (absolute) path to repository resource `RES`
`RES` | `XMLType` | The resource referred to by column `PATH`
`LINK` | `XMLType` | Link property
`RESID` | `RAW` | Resource OID

Figure 22-2 illustrates the structure of `RESOURCE_VIEW` and `PATH_VIEW`.

Note: Each resource may have multiple paths, called links.

The path in the `RESOURCE_VIEW` is an arbitrary one and one of the accessible paths that can be used to access that resource. Oracle XML DB provides SQL function `under_path`, which enables applications to search for resources contained (recursively) within a particular folder, get the resource depth, and so on. Each row in the `PATH_VIEW` and `RESOURCE_VIEW` columns is of `XMLType`. DML on repository views can be used to insert, rename, delete, and update resource properties and contents. Programmatic APIs must be used for some operations, such as creating links to existing resources.

Paths in the `ANY_PATH` column of the `RESOURCE_VIEW` and the `PATH` column in the `PATH_VIEW` are absolute paths: they start at the root.

Paths returned by the `path` function are relative paths under the path name specified by function `under_path`. For example, if there are two resources referenced by path names `/a/b/c` and `/a/d`, respectively, then a `path` expression that retrieves paths under folder `/a` will return relative paths `b/c` and `d`.

When there are multiple links to the same resource, only paths under the path name specified by function `under_path` are returned. Suppose `/a/b/c`, `/a/b/d` and `/a/e` are links to the same resource, a query on the `PATH_VIEW` that retrieves all the paths under `/a/b` return only `/a/b/c` and `/a/b/d`, not `/a/e`.

Figure 22-2 RESOURCE_VIEW and PATH_VIEW Structure

Description of "Figure 22-2 RESOURCE_VIEW and PATH_VIEW Structure"

### Understanding the Difference Between RESOURCE_VIEW and PATH_VIEW

Views `RESOURCE_VIEW` and `PATH_VIEW` differ as follows:

- `PATH_VIEW` displays all the path names to a particular resource. `RESOURCE_VIEW` displays one of the possible path names to the resource

- `PATH_VIEW` also displays the properties of the link

Figure 22-3 illustrates this difference between `RESOURCE_VIEW` and `PATH_VIEW`.

Because many Internet applications only need one URL to access a resource, `RESOURCE_VIEW` is widely applicable.

`PATH_VIEW` contains the link properties as well as resource properties, whereas the `RESOURCE_VIEW` only contains resource properties.

The `RESOURCE_VIEW` benefit is generally optimization. If the database knows that only one path is needed, then the index does not have to do as much work to determine all the possible paths.

Note: When using the `RESOURCE_VIEW`, if you are specifying a path with functions `under_path` or `equals_path`, then they will find the resource regardless of whether or not that path is the arbitrary one chosen to normally appear with that resource using `RESOURCE_VIEW`.

Figure 22-3 RESOURCE_VIEW and PATH_VIEW Explained

Description of "Figure 22-3 RESOURCE_VIEW and PATH_VIEW Explained"

### Operations You Can Perform Using UNDER_PATH and EQUALS_PATH

You can perform the following operations using `under_path` and `equals_path`:

- Given a path name:

  - Get a resource or its OID

  - List the directory given by the path name

  - Create a resource

  - Delete a resource

  - Update a resource

- Given a condition, containing SQL function `under_path` or other SQL functions:

  - Update resources

  - Delete resources

  - Get resources or their OID

See the "Using the RESOURCE_VIEW and PATH_VIEW APIs" and `equals_path`.

## RESOURCE_VIEW and PATH_VIEW APIs

This section describes the SQL functions applicable to `RESOURCE_VIEW` and `PATH_VIEW`.

### UNDER_PATH SQL Function

SQL function `under_path` uses the Oracle XML DB Repository hierarchical index to return the paths under a particular path. The hierarchical index is designed to speed access walking down a path name (the normal usage).

If the other parts of the query predicate are very selective, however, then a functional implementation of `under_path` can be chosen that walks back up the repository. This can be more efficient, because a much smaller number of links are required to be traversed. Figure 22-4 shows the `under_path` syntax.

Figure 22-4 UNDER_PATH Syntax

Description of "Figure 22-4 UNDER_PATH Syntax"

Table 22-3 details the `under_path` syntax.

Table 22-3 RESOURCE_VIEW and PATH_VIEW API Syntax: UNDER_PATH

Syntax | Description
--- | ---
`under_path(resource_column, pathname);` | Determines if a resource is under a specified path. Parameters: `resource_column` - The column name or column alias of the `RESOURCE` column in the `PATH_VIEW` or `RESOURCE_VIEW.` `pathname` - The path name to resolve.
`under_path(resource_column, depth, pathname);` | Determines if a resource is under a specified path, with a depth argument to restrict the number of levels to search. Parameters: `resource_column` - The column name or column alias of the `RESOURCE` column in the `PATH_VIEW` or `RESOURCE_VIEW``.` `depth` - The maximum depth to search. A nonnegative integer. `pathname` - The path name to resolve.
`under_path(resource_column, pathname, correlation);` | Determines if a resource is under a specified path, with a correlation argument for related SQL functions. Parameters: `resource_column` - The column name or column alias of the `RESOURCE` column in the `PATH_VIEW` or `RESOURCE_VIEW``.` `pathname` - The path name to resolve. `correlation` - An integer that can be used to correlate `under_path` with related SQL functions (`path` and `depth`).
`under_path(resource_column, depth, pathname, correlation);` | Determines if a resource is under a specified path with a depth argument to restrict the number of levels to search, and with a correlation argument for related SQL functions. Parameters: `resource_column` - The column name or column alias of the `RESOURCE` column in the `PATH_VIEW` or `RESOURCE_VIEW`. `depth` - The maximum depth to search. A nonnegative integer. `pathname` - The path name to resolve. `correlation` - An integer that can be used to correlate `under_path` with related SQL functions (`path` and `depth`). Note that only one of the accessible paths to the resource must be under the path argument for a resource to be returned.

### EQUALS_PATH SQL Function

SQL function `equals_path` is used to find the resource with the specified path name. It is functionally equivalent to `under_path` with a depth restriction of zero.

    equals_path(resource_column, pathname);

where:

- `resource_column` is the column name or column alias of the `RESOURCE` column in the `PATH_VIEW` or `RESOURCE_VIEW.`

- `pathname` is the path name to resolve.

Figure 22-5 illustrates the complete `equals_path` syntax.

Figure 22-5 EQUALS_PATH Syntax

Description of "Figure 22-5 EQUALS_PATH Syntax"

### PATH SQL Function

SQL function `path` returns the relative path name of the resource under the specified `pathname` argument. Note that the path column in the `RESOURCE_VIEW` always contains the absolute path of the resource. The `path` syntax is:

    path(correlation);

where:

- `correlation` is an integer that can be used to correlate `under_path` with related SQL functions (`path` and `depth`).

  Note: If a path is not under the specified `pathname` argument, a `NULL` value is returned as the output of the current path.

Figure 22-6 illustrates the `path` syntax.

Figure 22-6 PATH Syntax

Description of "Figure 22-6 PATH Syntax"

### DEPTH SQL Function

SQL function `depth` returns the folder depth of the resource under the specified starting path.

    depth(correlation);

where:

`correlation` is an integer that can be used to correlate `under_path` with related SQL functions (`path` and `depth`).

## Using the RESOURCE_VIEW and PATH_VIEW APIs

The following `RESOURCE_VIEW` and `PATH_VIEW` examples use SQL functions `under_path`, `equals_path`, `path`, and `depth`.

### Accessing Repository Data Paths, Resources and Links: Examples

The following examples illustrate how you can access paths, resources, and link properties in Oracle XML DB Repository. The first few examples use resources specified by the following paths:

    /a/b/c
    /a/b/c/d
    /a/e/c
    /a/e/c/d

Example 22-1 Determining Paths Under a Path: Relative

This example uses SQL function `path` to retrieve the relative paths under path `/a/b`.

    SELECT path(1) FROM RESOURCE_VIEW WHERE under_path(RES, '/a/b', 1) = 1;

Returns the following:

    PATH(1)
    -------
    c
    c/d

    2 rows selected.

Example 22-2 Determining Paths Under a Path: Absolute

This example uses `ANY_PATH` to retrieve the absolute paths under path `/a/b`.

    SELECT ANY_PATH FROM RESOURCE_VIEW WHERE under_path(RES, '/a/b') = 1;

This returns the following:

    ANY_PATH
    --------
    /a/b/c
    /a/b/c/d

    2 rows selected.

Example 22-3 Determining Paths Not Under a Path

This is the same example as Example 22-2, except that the test is not-equals (`!=`) instead of equals (`=`). This query finds all paths in the repository that are not under path `/a/b`.

    SELECT ANY_PATH FROM RESOURCE_VIEW WHERE under_path(RES, '/a/b') != 1

This produces a result like the following:

    ANY_PATH
    --------
    /a
    /a/b
    /a/e
    /a/e/c
    /a/e/c/d
    /home
    /home/OE
    /home/OE/PurchaseOrders
    /home/OE/PurchaseOrders/2002
    /home/OE/PurchaseOrders/2002/Apr
    /home/OE/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336171PDT.xml
    /home/OE/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336271PDT.xml
    /home/OE/PurchaseOrders/2002/Apr/EABEL-20021009123336251PDT.xml
    . . .
    /public
    /sys
    /sys/acls
    /sys/acls/all_all_acl.xml
    /sys/acls/all_owner_acl.xml
    /sys/acls/bootstrap_acl.xml
    /sys/acls/ro_all_acl.xml
    /sys/apps
    /sys/databaseSummary.xml
    /sys/log
    /sys/schemas
    /sys/schemas/OE
    /sys/schemas/OE/localhost:8080
    . . .

    202 rows selected.

Example 22-4 Determining Paths Using Multiple Correlations

    SELECT ANY_PATH, path(1), path(2)
      FROM RESOURCE_VIEW
      WHERE under_path(RES, '/a/b', 1) = 1 OR under_path(RES, '/a/e', 2) = 1;

This returns the following:

    ANY_PATH   PATH(1)  PATH(2)
    ---------- -------- --------
    /a/b/c     c
    /a/b/c/d   c/d
    /a/e/c              c
    /a/e/c/d            c/d

    4 rows selected.

To obtain all of the resources under a directory, you can use `LIKE` , as shown in Example 22-5. To obtain all of the resources up to a certain number of levels or to obtain the relative path, use SQL function `under_path`, as shown in Example 22-7. Example 22-5 is more effiicient than Example 22-7.

Example 22-5 Using ANY_PATH with LIKE

    SELECT ANY_PATH FROM RESOURCE_VIEW WHERE ANY_PATH LIKE '/sys%';

This produces a result like the following:

    ANY_PATH
    --------
    /sys
    /sys/acls
    /sys/acls/all_all_acl.xml
    /sys/acls/all_owner_acl.xml
    /sys/acls/bootstrap_acl.xml
    /sys/acls/ro_all_acl.xml
    /sys/apps
    /sys/databaseSummary.xml
    /sys/log
    /sys/schemas
    /sys/schemas/OE
    /sys/schemas/OE/localhost:8080
    /sys/schemas/OE/localhost:8080/source
    /sys/schemas/OE/localhost:8080/source/schemas
    /sys/schemas/OE/localhost:8080/source/schemas/poSource
    /sys/schemas/OE/localhost:8080/source/schemas/poSource/xsd
    /sys/schemas/OE/localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd
    /sys/schemas/PUBLIC
    /sys/schemas/PUBLIC/www.w3.org
    /sys/schemas/PUBLIC/www.w3.org/2001
    /sys/schemas/PUBLIC/www.w3.org/2001/xml.xsd
    /sys/schemas/PUBLIC/xmlns.oracle.com
    . . .

    42 rows selected.

Example 22-6 Relative Path Names for Three Levels of Resources

    SELECT path(1) FROM RESOURCE_VIEW WHERE under_path (RES, 3, '/sys', 1) = 1;

This produces a result like the following:

    PATH(1)
    -------
    acls
    acls/all_all_acl.xml
    acls/all_owner_acl.xml
    acls/bootstrap_acl.xml
    acls/ro_all_acl.xml
    apps
    databaseSummary.xml
    log
    schemas
    schemas/OE
    schemas/OE/localhost:8080
    schemas/PUBLIC
    schemas/PUBLIC/www.w3.org
    schemas/PUBLIC/xmlns.oracle.com

    14 rows selected.

Example 22-7 Extracting Resource Metadata using UNDER_PATH

    SELECT ANY_PATH, extract(RES, '/Resource') FROM RESOURCE_VIEW
       WHERE under_path(RES, '/sys') = 1;

This produces a result like the following:

    ANY_PATH
    --------
    EXTRACT(RES,'/RESOURCE')
    ------------------------
    /sys/acls
    <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd">
      <CreationDate>2005-02-07T18:31:53.093179</CreationDate>
      <ModificationDate>2005-02-07T18:31:55.852963</ModificationDate>
      <DisplayName>acls</DisplayName>
      <Language>en-US</Language>
      <CharacterSet>ISO-8859-1</CharacterSet>
      <ContentType>application/octet-stream</ContentType>
      <RefCount>1</RefCount>
    </Resource>

    /sys/acls/all_all_acl.xml
    <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd">
      <CreationDate>2005-02-07T18:31:55.745970</CreationDate>
      <ModificationDate>2005-02-07T18:31:55.745970</ModificationDate>
      <DisplayName>all_all_acl.xml</DisplayName>
      <Language>en-US</Language>
      <CharacterSet>ISO-8859-1</CharacterSet>
      <ContentType>text/xml</ContentType>
      <RefCount>1</RefCount>
    </Resource>
    . . .
    41 rows selected.

Example 22-8 Using Functions PATH and DEPTH with PATH_VIEW

    SELECT path(1) path, depth(1) depth FROM PATH_VIEW
      WHERE under_path(RES, 3, '/sys', 1) = 1;

This produces a result like the following:

    PATH                                      DEPTH
    ----                                      -----
    acls                                      1
    acls/all_all_acl.xml                      2
    acls/all_owner_acl.xml                    2
    acls/bootstrap_acl.xml                    2
    acls/ro_all_acl.xml                       2
    apps                                      1
    databaseSummary.xml                       1
    log                                       1
    schemas                                   1
    schemas/OE                                2
    schemas/OE/localhost:8080                 3
    schemas/PUBLIC                            2
    schemas/PUBLIC/www.w3.org                 3
    schemas/PUBLIC/xmlns.oracle.com           3

    14 rows selected.

Example 22-9 Extracting Link and Resource Information from PATH_VIEW

    SELECT PATH,
           extract(LINK, '/LINK/Name/text()').getstringval(),
           extract(LINK, '/LINK/ParentName/text()').getstringval(),
           extract(LINK, '/LINK/ChildName/text()').getstringval(),
           extract(RES,  '/Resource/DisplayName/text()').getstringval()
      FROM PATH_VIEW
      WHERE PATH LIKE '/sys%';

This produces a result like the following:

    /sys
    sys
    /
    sys
    sys

    /sys/acls
    acls
    sys
    acls
    acls

    /sys/acls/all_all_acl.xml
    all_all_acl.xml
    acls
    all_all_acl.xml
    all_all_acl.xml

    /sys/acls/all_owner_acl.xml
    all_owner_acl.xml
    acls
    all_owner_acl.xml
    all_owner_acl.xml

    /sys/acls/bootstrap_acl.xml
    bootstrap_acl.xml
    acls
    bootstrap_acl.xml
    bootstrap_acl.xml
    . . .

    42 rows selected.

Example 22-10 All Paths to a Certain Depth Under a Path

    SELECT path(1) FROM PATH_VIEW WHERE under_path(RES, 3, '/sys', 1) > 0 ;

This produces a result like the following:

    PATH(1)
    -------
    schemas
    acls
    log
    schemas/PUBLIC
    schemas/PUBLIC/xmlns.oracle.com
    acls/bootstrap_acl.xml
    acls/all_all_acl.xml
    acls/all_owner_acl.xml
    acls/ro_all_acl.xml
    schemas/PUBLIC/www.w3.org
    apps
    databaseSummary.xml

    12 rows selected.

Example 22-11 Using EQUALS_PATH to Locate a Path

    SELECT ANY_PATH FROM RESOURCE_VIEW WHERE equals_path(RES, '/sys') > 0;

This produces the following result:

    ANY_PATH
    --------
    /sys

    1 row selected.

Example 22-12 Retrieve RESID of a Given Resource

    SELECT RESID FROM RESOURCE_VIEW
      WHERE extract(RES, '/Resource/Dispname') = 'example';

This produces a result like the following:

    RESID
    --------------------------------
    F301A10152470252E030578CB00B432B

    1 row selected.

Example 22-13 Obtaining the Path Name of a Resource from its RESID

    DECLARE
      resid_example RAW(16);
      path          VARCHAR2(4000);
    BEGIN
      SELECT RESID INTO resid_example FROM RESOURCE_VIEW
        WHERE extractValue(RES, '/Resource/DisplayName') = 'example';
      SELECT ANY_PATH INTO path FROM RESOURCE_VIEW WHERE RESID = resid_example;
      DBMS_OUTPUT.put_line('The path is: ' || path);
    END;
    /
    The path is: /public/example

    PL/SQL procedure successfully completed.

Example 22-14 Folders Under a Given Path

    SELECT ANY_PATH FROM RESOURCE_VIEW
      WHERE under_path(RES, 1, '/sys') = 1
        AND existsNode(RES, '/Resource[@Container="true"]') = 1;

This produces a result like the following:

    ANY_PATH
    --------
    /sys/acls
    /sys/apps
    /sys/log
    /sys/schemas

    4 rows selected.

Example 22-15 Joining RESOURCE_VIEW with an XMLType Table

    SELECT ANY_PATH, extract(value(e), '/PurchaseOrder/LineItems').getclobval()
      FROM purchaseorder e, RESOURCE_VIEW r
      WHERE extractValue(r.RES, '/Resource/XMLRef') = ref(e) AND ROWNUM < 2;

This produces the following result:

    ANY_PATH
    --------------------------------------------------------------------------------
    EXTRACT(VALUE(E),'/PURCHASEORDER/LINEITEMS').GETCLOBVAL()
    --------------------------------------------------------------------------------
    /home/OE/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336171PDT.xml
    <LineItems>
      <LineItem ItemNumber="1">
        <Description>Salesman</Description>
        <Part Id="37429158920" UnitPrice="39.95" Quantity="2"/>
      </LineItem>
      <LineItem ItemNumber="2">
        <Description>Big Deal on Madonna Street</Description>
        <Part Id="37429155424" UnitPrice="29.95" Quantity="1"/>
      </LineItem>
      <LineItem ItemNumber="3">
        <Description>Hearts and Minds</Description>
        <Part Id="37429166321" UnitPrice="39.95" Quantity="1"/>
      </LineItem>

    . . .

      <LineItem ItemNumber="23">
        <Description>Great Expectations</Description>
        <Part Id="37429128022" UnitPrice="39.95" Quantity="4"/>
      </LineItem>
    </LineItems>

    1 row selected.

### Deleting Repository Resources: Examples

The following examples illustrate how you can delete resources and paths.

Example 22-16 Deleting Resources

If you delete only leaf resources, then you can use `DELETE FROM RESOURCE_VIEW`:

    DELETE FROM RESOURCE_VIEW WHERE ANY_PATH = '/public/myfile';

For multiple links to the same resource, deleting from `RESOURCE_VIEW` deletes the resource together with all of its links; deleting from `PATH_VIEW` deletes only the link with the specified path.

Example 22-17 Deleting Links to Resources

For example, suppose `'/home/myfile1'` is a link to `'/public/myfile'`:

    CALL DBMS_XDB.link('/public/myfile', '/home', 'myfile1');

This SQL DML statement deletes everything in Oracle XML DB Repository that is found at path `/home/myfile1` – both the link and the resource:

    DELETE FROM RESOURCE_VIEW WHERE equals_path(RES, '/home/myfile1') = 1;

This DML statement deletes only the link with path `/home/file1`:

    DELETE FROM PATH_VIEW WHERE equals_path(RES, '/home/file1') = 1;

#### Deleting Nonempty Folder Resources

The `DELETE` DML operator is not allowed on a nonempty folder. If you try to delete a nonempty folder, you must first delete its contents and then delete the resulting empty folder. This rule must of course be applied recursively to any folders contained in the target folder.

However, the order of the paths returned from a `WHERE` clause is not guaranteed, and the `DELETE` operator does not allow an `ORDER BY` clause in its table-expression subclause. This means that you cannot do the following:

    DELETE FROM (SELECT 1 FROM RESOURCE_VIEW
                   WHERE under_path(RES, '/public', 1) = 1
                   ORDER BY depth(1) DESCENDING);

Example 22-18 illustrates how to delete a nonempty folder.

Example 22-18 Deleting a Nonempty Folder

In this example, folder `example` is deleted, along with its subfolder `example1`.

    SELECT PATH FROM PATH_VIEW WHERE under_path(RES, '/home/US1') = 1;

    PATH
    --------------------------
    /home/US1/example
    /home/US1/example/example1

    2 rows selected.

    DECLARE
      CURSOR c1 IS
        SELECT ANY_PATH p FROM RESOURCE_VIEW
          WHERE under_path(RES, '/home/US1', 1) = 1
            AND existsNode(RES, '/Resource[Owner="US1"]') = 1
          ORDER BY depth(1) DESC;
      del_stmt VARCHAR2(500)
        := 'DELETE FROM RESOURCE_VIEW WHERE equals_path(RES, :1)=1';
    BEGIN
      FOR r1 IN c1 LOOP
        EXECUTE IMMEDIATE del_stmt USING r1.p;
      END LOOP;
    END;
    /

    PL/SQL procedure successfully completed.

    SELECT PATH FROM PATH_VIEW WHERE under_path(RES, '/home/US1') = 1;

    no rows selected

Note: As always, care should be taken to avoid deadlocks with concurrent transactions when operating on multiple rows.

### Updating Repository Resources: Examples

This section illustrates how to update resources and paths.

Example 22-19 Updating a Resource

This example changes the resource at path `/test/HR/example/paper`. This is the complete resource before the update:

    SELECT r.RES.getClobVal()
      FROM RESOURCE_VIEW r WHERE equals_path(RES, '/test/HR/example/paper') = 1;

    R.RES.GETCLOBVAL()
    --------------------------------------------------------------------------------
    <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" Hidden="false" Inv
    alid="false" Container="false" CustomRslv="false" VersionHistory="false" StickyR
    ef="true">
      <CreationDate>2005-04-29T16:30:01.588835</CreationDate>
      <ModificationDate>2005-04-29T16:30:01.588835</ModificationDate>
      <DisplayName>paper</DisplayName>
      <Language>en-US</Language>
      <CharacterSet>ISO-8859-1</CharacterSet>
      <ContentType>application/octet-stream</ContentType>
      <RefCount>1</RefCount>
      <ACL>
        <acl description="Public:All privileges to PUBLIC" xmlns="http://xmlns.oracl
    e.com/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:sch
    emaLocation="http://xmlns.oracle.com/xdb/acl.xsd                           http:
    //xmlns.oracle.com/xdb/acl.xsd">
          <ace>
            <principal>PUBLIC</principal>
            <grant>true</grant>
            <privilege>
              <all/>
            </privilege>
          </ace>
        </acl>
      </ACL>
      <Owner>TESTUSER1</Owner>
      <Creator>TESTUSER1</Creator>
      <LastModifier>TESTUSER1</LastModifier>
      <SchemaElement>http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary</SchemaElement
    >
      <Contents>
        <binary>4F7261636C65206F7220554E4958</binary>
      </Contents>
    </Resource>

    1 row selected.

All of the XML elements shown above are resource metadata elements, with the exception of `Contents`, which contains the resource content.

This `UPDATE` statement updates (only) the `DisplayName` metadata element.

    UPDATE RESOURCE_VIEW r
      SET r.RES = updateXML(r.RES, '/Resource/DisplayName/text()', 'My New Paper')
        WHERE ANY_PATH='/test/HR/example/paper';

    1 row updated.

    SELECT r.RES.getClobVal()
      FROM RESOURCE_VIEW r WHERE equals_path(RES, '/test/HR/example/paper') = 1;

    R.RES.GETCLOBVAL()
    --------------------------------------------------------------------------------
    <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" Hidden="false" Inv
    alid="false" Container="false" CustomRslv="false" VersionHistory="false" StickyR
    ef="true">
      <CreationDate>2005-04-29T16:30:01.588835</CreationDate>
      <ModificationDate>2005-04-29T16:30:01.883838</ModificationDate>
      <DisplayName>My New Paper</DisplayName>
      <Language>en-US</Language>

      . . .

      <Contents>
        <binary>4F7261636C65206F7220554E4958</binary>
      </Contents>
    </Resource>

    1 row selected.

See Also: Chapter 26, "User-Defined Repository Metadata" for additional examples of updating resource metadata

Note that, by default, the `DisplayName` element content, `paper`, was the same text as the last location step of the resource path, `/test/HR/example/paper`. This is only the default value, however. The `DisplayName` is independent of the resource path, so updating it does not change the path.

Element `DisplayName` is defined by the WebDAV standard, and it is recognized by WebDAV applications. Non-WebDAV applications, such as an FTP client, will not recognize the `DisplayName` of a resource. An FTP client lists the above resource as `paper` (using FTP command `ls`, for example) even after the `UPDATE` operation.

Example 22-20 Updating a Path in the PATH_VIEW

This example changes the path for the above resource from `/test/HR/example/paper` to `/test/myexample`. It is analogous to using the Unix command `mv /test/HR/example/paper /test/myexample`.

    SELECT ANY_PATH FROM RESOURCE_VIEW WHERE under_path(RES, '/test') = 1;

    ANY_PATH
    --------
    /test/HR
    /test/HR/example
    /test/HR/example/paper

    3 rows selected.

    UPDATE PATH_VIEW
      SET PATH = '/test/myexample' WHERE PATH = '/test/HR/example/paper';

    ANY_PATH
    --------
    /test/HR
    /test/HR/example
    /test/myexample

    3 rows selected.

See Also: Table 20-3, "Accessing Oracle XML DB Repository: API Options" for additional examples that use the SQL functions that apply to `RESOURCE_VIEW` and `PATH_VIEW`

## Working with Multiple Oracle XML DB Resources

The repository operations listed in Table 20-3 typically apply to a single resource at a time. To perform the same operation on multiple Oracle XML DB resources, or to find one or more Oracle XML DB resources that meet a certain set of criteria, use SQL with `RESOURCE_VIEW` and `PATH_VIEW`.

For example, you can perform the following operations:

- Updating based on attributes – see Example 22-21

- Finding resources inside a folder – see Example 22-22

- Copying a set of Oracle XML DB resources – see Example 22-23

Example 22-21 Updating Resources Based on Attributes

    UPDATE RESOURCE_VIEW
      SET RES = updateXML(RES, '/Resource/DisplayName/text()', 'My New Paper')
        WHERE extractValue(resource, '/Resource/DisplayName') = 'My Paper';

    SELECT ANY_PATH FROM RESOURCE_VIEW
      WHERE extractValue(RES, '/Resource/DisplayName') = 'My New Paper';

    ANY_PATH
    ---------------
    /test/myexample

    1 row selected.

Example 22-22 Finding Resources Inside a Folder

    SELECT ANY_PATH FROM RESOURCE_VIEW
      WHERE under_path(resource, '/sys/schemas/PUBLIC/xmlns.oracle.com/xdb') = 1;

    ANY_PATH
    --------------------------------------------------------------
    /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/XDBFolderListing.xsd
    /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/XDBResource.xsd
    /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/XDBSchema.xsd
    /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/XDBStandard.xsd
    /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/acl.xsd
    /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/dav.xsd
    /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/log
    /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/log/ftplog.xsd
    /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/log/httplog.xsd
    /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/log/xdblog.xsd
    /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/stats.xsd
    /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/xdbconfig.xsd

    12 rows selected.

Example 22-23 Copying Resources

This SQL DML statement copies all of the resources in folder `public` to folder `newlocation`. It is analogous to the Unix command `cp /public/* /newlocation`. Target folder `newlocation` must exist before the copy.

    SELECT PATH FROM PATH_VIEW WHERE under_path(RES, '/test') = 1;

    PATH
    -----------------
    /test/HR
    /test/HR/example
    /test/myexample

    3 rows selected.

    INSERT INTO PATH_VIEW
      SELECT '/newlocation/' || path(1), RES, LINK, NULL FROM PATH_VIEW
        WHERE under_path(RES, '/test', 1) = 1
        ORDER BY depth(1);

    3 rows created.

    SELECT PATH FROM PATH_VIEW WHERE under_path(RES, '/newlocation') = 1;

    PATH
    ------------------------
    /newlocation/HR
    /newlocation/HR/example
    /newlocation/myexample

    3 rows selected.

## Performance Tuning of Oracle XML DB Resource Queries

Oracle XML DB uses the `xdbconfig.xml` file for configuring the system and protocol environment. It includes an element parameter `resource-view-cache-size` that defines the in-memory size of the `RESOURCE_VIEW` cache. The default value is `1048576`.

The performance of some queries on `RESOURCE_VIEW` and `PATH_VIEW` can be improved by tuning `resource-view-cache-size`. In general, the bigger the cache size, the faster the query. The default `resource-view-cache-size` is appropriate for most cases, but you may want to enlarge your `resource-view-cache-size` element when querying a sizable `RESOURCE_VIEW`.

The extensible optimizer decides whether SQL functions `under_path` and `equals_path` are evaluated by a domain index scan or by functional implementation. To achieve the optimal query plan, the optimizer needs statistics for Oracle XML DB. Statistics can be collected by analyzing the Oracle XML DB tables and hierarchical index under `XDB` using the `ANALYZE` command or the `DBMS_STATS` package. The following is an example of using the `ANALYZE` command:

    ANALYZE TABLE XDB$H_LINK COMPUTE STATISTICS;
    ANALYZE TABLE XDB$RESOURCE COMPUTE STATISTICS;
    ANALYZE INDEX XDBHI_IDX DELETE STATISTICS;

The default limits for the following elements are soft limits. The system automatically adapts when these limits are exceeded.

- `XDBCORE-LOADABLEUNIT-SIZE` - This element indicates the maximum size to which a loadable unit (partition) can grow in Kilobytes. When a partition is read into memory or a partition is built while consuming a new document, the partition is built until it reaches the maximum size. The default value is 16 Kb.

- `XDBCORE-XOBMEM-BOUND` - This element indicates the maximum memory in kilobytes that a document is allowed to occupy. The default value is 1024 Kb. Once the document exceeds this number, some loadable units (partitions) are swapped out.

See Also: Chapter 28, "Administering Oracle XML DB"

## Searching for Resources Using Oracle Text

Table `XDB$RESOURCE` in database schema `XDB` stores the metadata and content of repository resources. You can search for resources that contain a specific keyword by using SQL function `contains` with `RESOURCE_VIEW` or `PATH_VIEW`.

Example 22-24 Find All Resources Containing "Paper"

    SELECT PATH FROM PATH_VIEW WHERE contains(RES, 'Paper') > 0;

    PATH
    -----------------------
    /newlocation/myexample
    /test/myexample

    2 rows selected.

Example 22-25 Find All Resources Containing "Paper" that are Under a Specified Path

    SELECT ANY_PATH FROM RESOURCE_VIEW
       WHERE contains(RES, 'Paper') > 0 AND under_path(RES, '/test') > 0;

    ANY_PATH
    ----------------
    /test/myexample

    1 row selected.

To evaluate such queries, you must first create a Context Index on the `XDB$RESOURCE` table. Depending on the type of documents stored in Oracle XML DB, choose one of the following options for creating your Context Index:

- If Oracle XML DB contains only XML documents, that is, no binary data, a regular Context Index can be created on the `XDB$RESOURCE` table. This is the case for Example 22-25.

      CREATE INDEX xdb$resource_ctx_i ON XDB.XDB$RESOURCE(OBJECT_VALUE)
        INDEXTYPE IS CTXSYS.CONTEXT;

  See Also: Chapter 4, "XMLType Operations" and Chapter 10, "Full-Text Search Over XML"

- If Oracle XML DB contains binary data such as Microsoft Word documents, a user filter is required to filter such documents prior to indexing. Use package `DBMS_XDBT` (`dbmsxdbt.sql`) to create and configure the Context Index.

      -- Install the package - connected as SYS
      @dbmsxdbt
      -- Create the preferences
      EXEC DBMS_XDBT.createPreferences;
      -- Create the index
      EXEC DBMS_XDBT.createIndex;

  See Also: Oracle Database PL/SQL Packages and Types Reference, for information on installing and using `DBMS_XDBT`. "APIs for XML"

Package `DBMS_XDBT` also includes procedures to synchronize and optimize the index. You can use procedure `configureAutoSync()` to configure automatically sync the index by using job queues.